﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SQLite;
using System.Data;
using System.Runtime.CompilerServices;
using System.IO;
using System.Reflection;

namespace WebApp.Models
{
    public class UserData
    {
        #region Table Names

        private static String STOCKS_TABLE = "stocks";
       
        #endregion

        #region Field Names

        private static String ID_FIELD = "_id";
        private static String FB_ID_FIELD = "fb_id";
        private static String SYMBOL_FIELD = "symbol";
        private static String BUYING_PRICE_FIELD = "buying_price";
        private static String VOLUME_FIELD = "volume";

        #endregion

        private static UserData instance = null;

        private SQLiteConnection connection = null;

        private UserData()
        {
            Connect();
        }

        public static UserData GetInstance()
        {
            if (instance == null)
                instance = new UserData();
            return instance;
        }

        private void Connect()
        {
            if (connection != null)
                return;
            connection = new SQLiteConnection("Data Source=:memory:");
            connection.Open();
            SQLiteCommand query = new SQLiteCommand(
                "CREATE TABLE IF NOT EXISTS " + STOCKS_TABLE +
                " (" + ID_FIELD + " INTEGER PRIMARY KEY  NOT NULL ," + FB_ID_FIELD +
                " TEXT NOT NULL ," + SYMBOL_FIELD + " TEXT NOT NULL ," + BUYING_PRICE_FIELD +
                " REAL," + VOLUME_FIELD + " INTEGER)", connection);
            query.ExecuteNonQuery();
        }

        public StockCollection GetStocks(Person person)
        {
            Connect();
            SQLiteCommand query = new SQLiteCommand(
                "SELECT * FROM " + STOCKS_TABLE + 
                " WHERE " + FB_ID_FIELD + " = \"" + person.Id + "\"",
                connection);
            SQLiteDataReader reader = query.ExecuteReader();
            List<Stock> stocks = new List<Stock>();
            if(reader == null || !reader.HasRows)
                return new StockCollection(stocks);
            while (reader.Read())
            {
                int symCol = reader.GetOrdinal(SYMBOL_FIELD);
                int buyingPriceCol = reader.GetOrdinal(BUYING_PRICE_FIELD);
                int volCol = reader.GetOrdinal(VOLUME_FIELD);
                String symbol = reader.GetString(symCol);
                Stock stock = new Stock(symbol);
                stock.BuyingPrice = reader.GetDouble(buyingPriceCol);
                stock.Shares = reader.GetInt64(volCol);
                stocks.Add(stock);
            }
            return new StockCollection(stocks);
        }

        public int RemoveStock(Person person, Stock stock)
        {
            SQLiteCommand query = new SQLiteCommand(
                "DELETE FROM " + STOCKS_TABLE +
                " WHERE " + FB_ID_FIELD + " = \"" + person.Id +
                "\" AND " + SYMBOL_FIELD + " = \"" + stock.Symbol + "\"",
                connection);
            return query.ExecuteNonQuery();
        }

        public int UpdateStock(Person person, Stock stock)
        {
            SQLiteCommand query = new SQLiteCommand(
                "UPDATE " + STOCKS_TABLE + 
                " SET " + BUYING_PRICE_FIELD + " = " + stock.BuyingPrice + 
                ", " + VOLUME_FIELD + " = " + stock.Shares + 
                " WHERE " + FB_ID_FIELD + " = \"" + person.Id + 
                "\" AND " + SYMBOL_FIELD + " = \"" + stock.Symbol + "\"",
                connection);
            return query.ExecuteNonQuery();
        }

        public int InsertStock(Person person, Stock stock)
        {
            SQLiteCommand query = new SQLiteCommand(
                "INSERT INTO " + STOCKS_TABLE +
                " (" + FB_ID_FIELD + ", " + SYMBOL_FIELD + 
                ", " + BUYING_PRICE_FIELD + ", " + VOLUME_FIELD + 
                ") VALUES (\"" + person.Id + "\", \"" + stock.Symbol + 
                "\", " + stock.BuyingPrice + ", " + stock.Shares + ")",
                connection);
            return query.ExecuteNonQuery();
        }
    }
}